BigQuery に対して外部 SQL ファイルを実行してみた

みかみ

2020.03.18

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

BigQuery 勉強中です。

GCP 管理コンソール以外から BigQuery に対して SQL の実行方法は確認しましたが、確認時、実行する SQL はコード（コマンド）にべた書きしてました。

でも、実際のシステムでは、SQL は別ファイルに外出しした方が管理しやすいものです。

BigQuery でも外部ファイル（SQLファイル）実行できるよね？（どうやるの？

やりたいこと

BigQuery に対して、別ファイル（ *.sql ）に記載したSQL文を実行したい

`bq` コマンドで実行

bq コマンド実行環境は準備できていることを前提としています。

参考：Google Cloud SDK をAmazon Linux 2環境にインストール(スクリプトベース＋検証コード利用) ｜ Developers.IO

bq コマンドで SQL ファイルを実行する方法を調べてみたら、そのものズバリな記事が！

参考：BigQueryのbqコマンドでsqlをファイルから実行するやり方

bq query コマンドにリダイレクトの標準入力でファイル内の SQL 渡してあげれば、難なく実行できるようです！（簡単でいいですねｖ

実際にやってみます。

BigQuery には、47都道府県データをロードした pref テーブルがある状態です。

このテーブルに対して、レコードを INSERT ＆ UPDATE する、下記 SQL ファイルを準備しました。

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (100, '新しい都道府県');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET name = '琉球' WHERE code = 100;

bq query コマンドで SQL ファイルを指定して実行してみます。

[ec2-user@ip-10-0-43-239 ~]$ bq query --use_legacy_sql=false < test_exec_file.sql
Waiting on bqjob_r31257d17c18626a4_00000170ed5d34c5_1 ... (5s) Current status: DONE

GCP 管理コンソールから確認してみると

レコードが INSERT ＆ UPDATE されていることが確認できました。

Python クライアントライブラリ（ `google-cloud-bigquery` ）で実行

続いて、Pythonコードから SQL ファイルを実行してみます。

BigQuery の Python クライアントライブラリ実行環境は準備できていることを前提としています。 Python 3.7.4 環境で確認しました。

以下の SQL ファイルと Python スクリプトを準備しました。

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (99, '甲斐');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET code = 1000 WHERE name = '甲斐';

from google.cloud import bigquery

path = '/home/ec2-user/test_exec_file_2.sql'
try:
    with open(path, 'r', encoding='utf-8') as f:
        query = f.read()
except Exception as e:
    raise e

client = bigquery.Client()
try:
    query_job = client.query(query)
except Exception as e:
    raise e

Python ファイルを実行してみます。

(test_bq) [ec2-user@ip-10-0-43-239 ~]$ python test.py
(test_bq) [ec2-user@ip-10-0-43-239 ~]$
(

GCP 管理コンソールから確認してみると

こちらも期待通り、レコードが INSERT ＆ UPDATE されていることが確認できました。

BigQuery のトランザクション処理を確認する

BigQueryでは、複数クエリの同一トランザクション実行（ロールバック）機能はないとのこと。。

各 DML ステートメントは、暗黙のトランザクションを開始します。つまり、成功した各 DML ステートメントの終了時に、ステートメントによる変更が自動的にコミットされます。複数ステートメントのトランザクションはサポートされていません。

引用：データ操作言語 | BigQuery ドキュメント

実際に確認してみました。

１つ目の INSERT クエリは成功するけれど、２つ目の UPDATE クエリで失敗する（存在しないカラムの値を UPDATE しようとする）、以下のSQL ファイルを準備しました。

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (9999, '越後');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET names = '越後製菓' WHERE code = 9999;

bq query コマンドで実行してみます。

(test_bq) [ec2-user@ip-10-0-43-239 ~]$ bq query --use_legacy_sql=false < test_exec_file_error.sql
Waiting on bqjob_r513e5590d32a6ee4_00000170ed748d8b_1 ... (2s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r513e5590d32a6ee4_00000170ed748d8b_1':
Query error: Unrecognized name: names; Did you mean name? at [2:52]

GCP 管理コンソールから結果を確認してみると